cd "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data"


// Dealscan file with facility pricing
use "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data\Dealscan-Legacy-5-23-2022\currfacpricing.dta", clear

gen x=(baserate=="LIBOR")
bysort facilityid: egen has_libor=max(x)


gen aisd=(minbps+maxbps)/2 if (baserate=="LIBOR" | fee=="Annual Regular Fee") & has_libor==1

gen aisu=(minbps+maxbps)/2 if baserate=="Annual Regular Fee" | fee=="Commitment Regular Fee"

gen fees=(minbps+maxbps)/2  if !mi(fee)


gen baserate_libor=(baserate=="LIBOR")
gen baserate_prime=(baserate=="Prime")
gen baserate_fixed=(baserate=="Fixed Rate")
gen baserate_euribor=(baserate=="Euribor")
gen baserate_hibor=(baserate=="HIBOR")
gen baserate_cd=(baserate=="CD")



collapse (first) allindrawn allinundrawn (sum) aisd aisu fees (max) baserate_* , by(facilityid)

replace fees=. if fees==0
replace aisd=. if aisd==0
replace aisu=. if aisu==0
order facilityid allindrawn aisd allinundrawn aisu

gen allindrawn_imp=allindrawn
replace allindrawn_imp=aisd if mi(allindrawn)

gen allinundrawn_imp=allinundrawn
replace allinundrawn_imp=aisu if mi(allinundrawn)

bysort facilityid: keep if _n==1
keep facilityid allindrawn  allindrawn_imp  allinundrawn allinundrawn_imp fees  baserate_*
save facilityid_spreads, replace

// Dealscan file with facility details
use "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data\Dealscan-Legacy-5-23-2022\facility.dta", clear

bysort facilityid: keep if _n==1
joinby facilityid using facilityid_spreads, unm(master)
tab _m
drop _m


tab maturity
gen secured_loan=(secured=="Yes")
gen revolver=regexm(lower(loantype),"revolver")

keep packageid facilityid  maturity  secured_loan allindrawn revolver allindrawn_imp allinundrawn allinundrawn_imp fees  baserate_*

collapse  (max) revolver maturity  secured_loan allindrawn_max=allindrawn allindrawn_imp_max=allindrawn_imp  allinundrawn_max=allinundrawn allinundrawn_imp_max=allinundrawn_imp fees_max=fees   baserate_* (min) allindrawn_imp_min=allindrawn_imp allindrawn_min=allindrawn fees_min=fees , by(packageid)

save packageid-chars, replace


